﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Text;

namespace Budget
{
    public static class DatabaseQuery
    {
        public static DataTable GetInfoFromStoredProc(string procToCall, StoredProcParams[] parameters)
        {
            OleDbDataReader reader = null;
            DataTable dt = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(ConfigurationManager.AppSettings["budgetConnectionString"].ToString()))
            {
                using (OleDbCommand cmd = new OleDbCommand(procToCall, conn))
                {
                    try
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        if(parameters != null)
                        {
                            foreach (StoredProcParams s in parameters)
                            {
                                cmd.Parameters.AddWithValue(s.ParamName, s.ParamValue);
                            }
                        }
                        conn.Open();
                        reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
                        dt.Load(reader);
                    }
                    catch { }
                    finally { if (conn.State == ConnectionState.Open)conn.Close(); if (reader != null)reader.Close(); }
                }
            }
            return dt;
        }
        public static void ExecQueryNoReturn(string query)
        {
            using (OleDbConnection conn = new OleDbConnection(ConfigurationManager.AppSettings["budgetConnectionString"].ToString()))
            {
                using (OleDbCommand cmd = new OleDbCommand(query, conn))
                {
                    try
                    {
                        conn.Open();
                        cmd.ExecuteReader();
                    }
                    catch { }
                    finally { if(conn.State == ConnectionState.Open)conn.Close();}
                }
            }
        }
        public static void ExecStoredProcNoReturn(string procToCall, StoredProcParams[] parameters)
        {
            OleDbDataReader reader = null;
            DataTable dt = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(ConfigurationManager.AppSettings["budgetConnectionString"].ToString()))
            {
                using (OleDbCommand cmd = new OleDbCommand(procToCall, conn))
                {
                    try
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (parameters != null)
                        {
                            foreach (StoredProcParams s in parameters)
                            {
                                OleDbParameter parm = new OleDbParameter();
                                parm.OleDbType = s.dbType;
                                parm.ParameterName = s.ParamName;
                                parm.Value = s.ParamValue;
                                cmd.Parameters.Add(parm);
                            }
                        }
                        conn.Open();
                        cmd.ExecuteReader(CommandBehavior.KeyInfo);
                    }
                    catch { }
                    finally { /*conn.Close();*/ if (reader != null)reader.Close(); }
                }
            }
        }
        public static DataTable ExecStringProc(string _command)
        {
            OleDbDataReader reader = null;
            DataTable dt = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(ConfigurationManager.AppSettings["budgetConnectionString"].ToString()))
            {
                using (OleDbCommand cmd = new OleDbCommand(_command, conn))
                {
                    try
                    {
                        cmd.CommandType = CommandType.Text;
                        conn.Open();
                        reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
                        dt.Load(reader);
                    }
                    catch { }
                    finally { if (conn.State == ConnectionState.Open)conn.Close(); if (reader != null)reader.Close(); }
                }
            }
            return dt;
        }
        public static void ExecStringProcNoReturn(string _command)
        {
            OleDbDataReader reader = null;
            using (OleDbConnection conn = new OleDbConnection(ConfigurationManager.AppSettings["budgetConnectionString"].ToString()))
            {
                using (OleDbCommand cmd = new OleDbCommand(_command, conn))
                {
                    try
                    {
                        cmd.CommandType = CommandType.Text;
                        conn.Open();
                        cmd.ExecuteReader(CommandBehavior.KeyInfo);
                    }
                    catch { throw; }
                    finally { /*conn.Close();*/ if (reader != null)reader.Close(); }
                }
            }
        }
    }

    public class StoredProcParams
    {
        public string ParamName;
        public string ParamValue;
        public OleDbType dbType;

        public StoredProcParams(string name, string value)
        {
            switch (name)
            {
                case "amount":
                    dbType = OleDbType.Currency;
                    break;

                default:
                    dbType = OleDbType.VarWChar;
                    break;
            }
            ParamName = name;
            ParamValue = value;
        }
    }
    public static class StoredProcs
    {
        public static string getIncomeForWeek(string _datestart, string _dateend)
        {
            return String.Format("SELECT * FROM tblIncome " +
                "INNER JOIN tblCategoryIncome ON tblIncome.Category=tblCategoryIncome.ID " +
                "WHERE Date >= #{0}# AND Date <= #{1}#" +
                "ORDER BY tblIncome.Category, tblIncome.Date",
                _datestart, _dateend);
        }
        public static string InsertExpense(string _cat, string _amount, string _date, string _desc, string _acct, string _company, string _location)
        {
            string acct = _acct;
            if (string.IsNullOrEmpty(_acct))
            {
                acct = "null";
            }
            return String.Format("INSERT INTO tblExpenses "+
                "( Category, Amount, [Date], Description, AccountID, Company, Location ) VALUES "+
                "({0},{1}, #{2}#, '{3}', {4}, '{5}','{6}')",
                _cat,_amount,_date,_desc,acct, _company, _location);
        }
        public static string InsertIncome(string _cat, string _amount, string _date, string _desc, string _acct)
        {
            string acct = _acct;
            if (string.IsNullOrEmpty(_acct))
            {
                acct = "null";
            }
            return String.Format("INSERT INTO tblIncome " +
                "( Category, Amount, [Date], Description, AccountID ) VALUES " +
                "({0},{1}, #{2}#, '{3}', {4})",
                _cat, _amount, _date, _desc, acct);
        }
        public static string getExpenseForWeek(string _datestart, string _dateend)
        {
            return String.Format("SELECT * FROM tblExpenses "+
                " INNER JOIN tblCategoryExpense ON tblExpenses.Category=tblCategoryExpense.ID "+
                " WHERE Date >= #{0}# AND Date <= #{1}# " +
                " AND tblCategoryExpense.Hidden = 'F'" +
                "ORDER BY tblExpenses.Category, tblExpenses.Date",
                _datestart,_dateend);
        }
        public static string getExpenseForWeekByDate(string _datestart, string _dateend, string _filter, bool _IncludeFixed)
        {
            string fixedFilter = "";
            if(!_IncludeFixed)
            {
                fixedFilter = " AND tblCategoryExpense.Fixed = 0 ";
            }
            
            if (string.IsNullOrEmpty(_filter))
            {
                return String.Format("SELECT * FROM tblExpenses " +
                " INNER JOIN tblCategoryExpense ON tblExpenses.Category=tblCategoryExpense.ID " +
                " WHERE Date >= #{0}# AND Date <= #{1}# " +
                " AND tblCategoryExpense.Hidden = 'F' " +
                fixedFilter +
                " ORDER BY tblExpenses.Date,tblExpenses.Category ",
                _datestart, _dateend);
            }
            return String.Format("SELECT * FROM tblExpenses " +
                " INNER JOIN tblCategoryExpense ON tblExpenses.Category=tblCategoryExpense.ID " +
                " WHERE Date >= #{0}# AND Date <= #{1}# " +
                " AND tblCategoryExpense.Hidden = 'F' " +
                " AND ( Company LIKE '%{2}%' OR Description LIKE '%{3}%') " +
                fixedFilter +
                " ORDER BY tblExpenses.Date, tblExpenses.Category ",
                _datestart, _dateend, _filter, _filter);
        }
        public static string getExpenseCategoryList()
        {
            return String.Format("SELECT * FROM tblCategoryExpense " +
                " WHERE tblCategoryExpense.Hidden = 'F'"+
                " ORDER BY Fixed DESC, Category ASC");
        }
        public static string getIncomeCategoryList()
        {
            return String.Format("SELECT * FROM tblCategoryIncome " +
                //"WHERE tblCategoryIncome.Hidden <> 'T'"+
                "");
        }
        public static string getYearToDateByCategoryForMonth(string _datestart, string _dateend, string _cat)
        {
            return String.Format("SELECT * FROM tblExpenses " +
                " INNER JOIN tblCategoryExpense ON tblExpenses.Category=tblCategoryExpense.ID " +
                " WHERE Date >= #{0}# AND Date <= #{1}# "+
                " AND tblCategoryExpense.Category = '{2}'" +
                " ORDER BY tblExpenses.Date",
                _datestart, _dateend, _cat);
        }
        public static string getColumnAutoComplete(string _colname, string[] _args)
        { 
            string query;
            query = String.Format("SELECT DISTINCT({0}) FROM tblExpenses ", _colname);
            
            if(_args.Length == 0)
            {
                return query;
            }
            query += String.Format(" WHERE {0} LIKE '%{1}%'", _colname, _args[0]);
            for (int i = 1 ; i < _args.Length ; i++ )
            {
                query += String.Format(" OR {0} LIKE '%{1}%'", _colname, _args[0]);
            }
            return query;
        }
        public static string getExpenseForWeek(string _datestart, string _dateend, string _filter)
        {
            if(string.IsNullOrEmpty(_filter))
            {
                return String.Format("SELECT * FROM tblExpenses " +
                " INNER JOIN tblCategoryExpense ON tblExpenses.Category=tblCategoryExpense.ID " +
                " WHERE Date >= #{0}# AND Date <= #{1}# " +
                " AND tblCategoryExpense.Hidden = 'F'" +
                "ORDER BY tblExpenses.Category, tblExpenses.Date",
                _datestart, _dateend);
            }
            return String.Format("SELECT * FROM tblExpenses " +
                " INNER JOIN tblCategoryExpense ON tblExpenses.Category=tblCategoryExpense.ID " +
                " WHERE Date >= #{0}# AND Date <= #{1}# " +
                " AND tblCategoryExpense.Hidden = 'F' " +
                " AND ( Company LIKE '%{2}%' OR Description LIKE '%{3}%') " +
                " ORDER BY tblExpenses.Category, tblExpenses.Date ",
                _datestart, _dateend, _filter, _filter);
        }
        public static string getTransactionFromID(Money.TransactionType _tansType, int _id)
        {
            string returnStr;
            string tableName = "";
            switch (_tansType)
            {
                case Money.TransactionType.Expense:
                    tableName = "tblExpenses";
                    break;
                case Money.TransactionType.Income:
                    tableName = "tblIncome";
                    break;
            }
            returnStr = String.Format("SELECT * FROM {0} " +
                        "  " +
                        " WHERE ID={1} "
                        ,
                        tableName, _id.ToString());
            return returnStr;
        }
        public static string UpdateTransaction(Money.TransactionType _tansType, int _id, string _company, string _location, string _amount, DateTime _date, string _desc, string _category)
        {
            string returnStr;
            string tableName = "";
            switch (_tansType)
            {
                case Money.TransactionType.Expense:
                    tableName = "tblExpenses";
                    break;
                case Money.TransactionType.Income:
                    tableName = "tblIncome";
                    break;
            }
            returnStr = String.Format("UPDATE {0} " +
                        " SET " +
                        " Category = {1}, " +
                        " Company = '{2}', " +
                        " Location = '{3}', " +
                        " Amount = {4}, " +
                        " [Date] = #{5}#, " +
                        " Description = '{6}' " +
                        " WHERE ID={7} "
                        ,
                        tableName, 
                        _category,
                        _company,
                        _location,
                        _amount.Replace("$",""),
                        _date.ToString("MM/dd/yyyy"),
                        _desc,
                        _id.ToString());
            return returnStr;
        }
        public static string GetTransactionCategories(Money.TransactionType _tansType)
        {
            string tableName = "";
            switch (_tansType)
            {
                case Money.TransactionType.Expense:
                    tableName = "tblCategoryExpense";
                    break;
                case Money.TransactionType.Income:
                    tableName = "tblCategoryIncome";
                    break;
            }
            return String.Format("SELECT * from {0}", tableName);
        }
        public static string SetTransactionCategories(Money.TransactionType _tansType, int _id, string _category, string _hidden, string _budgetamt, string _fixed, string _luxury)
        {
            string tableName = "";
            switch (_tansType)
            {
                case Money.TransactionType.Expense:
                    tableName = "tblCategoryExpense";
                    return String.Format("UPDATE {0} SET "+
                        " Category = '{1}' " +
                        " Hidden = '{1}' " +
                        " BudgetAmount = {1} " +
                        " Fixed = {1} " +
                        " Luxury = {1} "
                        ,
                        tableName,
                        _category,
                        _hidden,
                        _budgetamt,
                        _fixed,
                        _luxury
                        );
                    break;
                case Money.TransactionType.Income:
                    tableName = "tblCategoryIncome";
                    return String.Format("UPDATE {0} SET " +
                        " Category = '{1}' " +
                        " Hidden = '{1}' "
                        // + " BudgetAmount = {1} " +
                        //" Fixed = {1} " +
                        //" Luxury = {1} "
                        ,
                        tableName,
                        _category,
                        _hidden
                        //,_budgetamt,
                        //_fixed,
                        //_luxury
                        );
                    break;
            }
            return String.Format("SELECT * from {0}", tableName);
        }
    }
}
